#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
my ($cmd);
use db_parameters;
use ensembl_parameters;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_taxon_gene_descriptions

	Description:
		Obtain ENSEMBL gene from the sanger database
	Populates DB tables:
		taxon.gene_descriptions

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE

#_________________________________________________________________________________________

#	retrieve_ensembl_taxon.prot_interpro_matches

#		retrieve data from ENSEMBL

#_________________________________________________________________________________________
sub retrieve_ensembl_gene_descriptions($)
{
	my ($taxon) = @_;
	my $ens_db_name = $taxon->[ENS_TAXON] .'_core_'.$taxon->[ENS_VERSION];
	my $dbh = DBI->connect("dbi:mysql:$ens_db_name:$ens_db_host", "$ens_db_user", '',
							{
								RaiseError => 1,
								AutoCommit => 1,
								PrintError => 0
							}
						)
	or die "Database connection not made: DBI::errstr()";

	my $ens_major_version = $taxon->[ENS_VERSION];
	$ens_major_version =~ s/_.*//;

	if ($ens_major_version <= 30)
	{
		my $array = $dbh->selectall_arrayref(<<'PL/SQLCMD');
		SELECT
            DISTINCT (stable_id)
			stable_id,
			description
		FROM
			gene_stable_id,
			gene_description
		WHERE
            gene_stable_id.gene_id = gene_description.gene_id AND
            description <> ''
PL/SQLCMD
		$dbh->disconnect;
		return $array;
	}
	else
	{
		my $array = $dbh->selectall_arrayref(<<'PL/SQLCMD');
		SELECT
            DISTINCT (stable_id)
			stable_id,
			description
		FROM
			gene_stable_id,
			gene
		WHERE
            gene_stable_id.gene_id = gene.gene_id AND
            description <> ''
PL/SQLCMD
		$dbh->disconnect;
		return $array;
	}

}







#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

# Main logic

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888


#
#	Get DB parameters
#
my @db_param = get_ensembl_taxon_data();
my $dbh = connect_to_panda();
$dbh->do("TRUNCATE taxon.gene_descriptions;");
$dbh->do("VACUUM ANALYSE taxon.gene_descriptions;");
for my $taxon_data(@db_param)
{

	#
	#	Retrieve from ENSEMBL
	#
	print STDERR "\t$taxon_data->[TAXON] gene descriptions...\n";
	print STDERR "\t\tRetrieving from Ensembl...\n";
	my $gene_descriptions = retrieve_ensembl_gene_descriptions($taxon_data);



	#
	#	Copy to panda
	#
	print STDERR "\tCopying ".scalar(@$gene_descriptions)." gene descriptions to panda\n";
	$dbh->do(<<"PL/SQLCMD");
		COPY taxon.gene_descriptions
				(gene_id, description) FROM STDIN;
PL/SQLCMD
	for my $desc(@$gene_descriptions)
	{
		$dbh->func(join ("\t",  @$desc)."\n", 'putline');
	}
	$dbh->func("\\.\n", 'putline');
	$dbh->func('endcopy');
}
$dbh->do("ANALYSE taxon.gene_descriptions;");
$dbh->disconnect;
print STDERR "\tCompleted\n";





